From ea695c235649a00d7d006dac2b76bd0f160737e7 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 20 Jul 2026 17:09:21 +0000 Subject: [PATCH] Create, document, index, and trigger CHIMPS_ON_TIKIS --- db/schemas/housekeeping/indexes/Makefile | 3 +- .../indexes/create/chimps_on_tikis.m4 | 42 +++++++ .../indexes/drop/chimps_on_tikis.m4 | 30 +++++ db/schemas/housekeeping/tables/Makefile | 3 +- .../tables/create/chimps_on_tikis.m4 | 37 ++++++ db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/chimps_on_tikis.m4 | 49 ++++++++ .../lib/triggers/drop/chimps_on_tikis.m4 | 23 ++++ doc/src/epilog.inc.m4 | 14 +++ doc/src/housekeeping.m4 | 1 + doc/src/housekeeping/chimps_on_tikis.m4 | 117 ++++++++++++++++++ 11 files changed, 319 insertions(+), 3 deletions(-) create mode 100644 db/schemas/housekeeping/indexes/create/chimps_on_tikis.m4 create mode 100644 db/schemas/housekeeping/indexes/drop/chimps_on_tikis.m4 create mode 100644 db/schemas/housekeeping/tables/create/chimps_on_tikis.m4 create mode 100644 db/schemas/lib/triggers/create/chimps_on_tikis.m4 create mode 100644 db/schemas/lib/triggers/drop/chimps_on_tikis.m4 create mode 100644 doc/src/housekeeping/chimps_on_tikis.m4 diff --git a/db/schemas/housekeeping/indexes/Makefile b/db/schemas/housekeeping/indexes/Makefile index 6b03fce..12642e3 100644 --- a/db/schemas/housekeeping/indexes/Makefile +++ b/db/schemas/housekeeping/indexes/Makefile @@ -19,7 +19,8 @@ # This determines the order in which the indexes are put into the database. # This is not really important but is needed for other types of db objects. -ORDER := aggression_event_log +ORDER := aggression_event_log \ + chimps_on_tikis ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/housekeeping/indexes/create/chimps_on_tikis.m4 b/db/schemas/housekeeping/indexes/create/chimps_on_tikis.m4 new file mode 100644 index 0000000..1583471 --- /dev/null +++ b/db/schemas/housekeeping/indexes/create/chimps_on_tikis.m4 @@ -0,0 +1,42 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +CREATE UNIQUE INDEX IF NOT EXISTS + "CommID + TikiCreationDate + AnimID must be unique" + ON chimps_on_tikis + (commid, animid, tikicreationdate); + +CREATE INDEX IF NOT EXISTS chimps_on_tikis_animid + ON chimps_on_tikis + (animid); +CREATE INDEX IF NOT EXISTS chimps_on_tikis_tikicreateiondate + ON chimps_on_tikis + (tikicreationdate); +CREATE INDEX IF NOT EXISTS chimps_on_tikis_tikideployementdate + ON chimps_on_tikis + (animid); +CREATE INDEX IF NOT EXISTS chimps_on_tikis_newcomer + ON chimps_on_tikis + (newcomer); + + diff --git a/db/schemas/housekeeping/indexes/drop/chimps_on_tikis.m4 b/db/schemas/housekeeping/indexes/drop/chimps_on_tikis.m4 new file mode 100644 index 0000000..e87b6db --- /dev/null +++ b/db/schemas/housekeeping/indexes/drop/chimps_on_tikis.m4 @@ -0,0 +1,30 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +DROP INDEX IF EXISTS + "CommID + TikiCreationDate + AnimID must be unique"; + +DROP INDEX IF EXISTS chimps_on_tikis_animid; +DROP INDEX IF EXISTS chimps_on_tikis_tikicreateiondate; +DROP INDEX IF EXISTS chimps_on_tikis_tikideployementdate; +DROP INDEX IF EXISTS chimps_on_tikis_newcomer; diff --git a/db/schemas/housekeeping/tables/Makefile b/db/schemas/housekeeping/tables/Makefile index 39b4ffb..5b6366c 100644 --- a/db/schemas/housekeeping/tables/Makefile +++ b/db/schemas/housekeeping/tables/Makefile @@ -20,7 +20,8 @@ # This determines the order in which the tables are put into the database. # This is important because the tables containing foreign keys must be put # into the database after the tables they reference. -ORDER := aggression_event_log +ORDER := aggression_event_log \ + chimps_on_tikis ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/housekeeping/tables/create/chimps_on_tikis.m4 b/db/schemas/housekeeping/tables/create/chimps_on_tikis.m4 new file mode 100644 index 0000000..d00119e --- /dev/null +++ b/db/schemas/housekeeping/tables/create/chimps_on_tikis.m4 @@ -0,0 +1,37 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`tablemacros.m4')dnl +include(`grants.m4')dnl +dnl + +CREATE TABLE chimps_on_tikis ( + key_column(`CHIMPS_ON_TIKIS', `ID', INTEGER) + ,animid_column() + ,commid TEXT NOT NULL + CONSTRAINT "CommID must be a COMM_IDS.CommID value" + REFERENCES comm_ids + ,tikicreationdate DATE NOT NULL + ,tikideploymentdate DATE NOT NULL + ,newcomer BOOLEAN NOT NULL +); + +grant_priv(`CHIMPS_ON_TIKIS') diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index ac07f63..1c601a1 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -55,7 +55,8 @@ ORDER := comm_ids \ elo_ranks_daily_kk_f \ elo_ranks_daily_kk_m \ elo_ranks_daily_mt_f \ - elo_ranks_daily_mt_m + elo_ranks_daily_mt_m \ + chimps_on_tikis DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/chimps_on_tikis.m4 b/db/schemas/lib/triggers/create/chimps_on_tikis.m4 new file mode 100644 index 0000000..ca75825 --- /dev/null +++ b/db/schemas/lib/triggers/create/chimps_on_tikis.m4 @@ -0,0 +1,49 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify it +dnl under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Triggers for the chimps_on_tikis table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'chimps_on_tikis_update_func'; +CREATE OR REPLACE FUNCTION chimps_on_tikis_update_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for chimps_on_tikis update trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + cannot_change(`CHIMPS_ON_TIKIS', `ID') + + RETURN NULL; + END; +$$; + + +RAISE INFO 'chimps_on_tikis_update_trigger'; +CREATE TRIGGER chimps_on_tikis_update_trigger + AFTER INSERT OR UPDATE + ON chimps_on_tikis FOR EACH ROW + EXECUTE PROCEDURE chimps_on_tikis_update_func(); diff --git a/db/schemas/lib/triggers/drop/chimps_on_tikis.m4 b/db/schemas/lib/triggers/drop/chimps_on_tikis.m4 new file mode 100644 index 0000000..10872b2 --- /dev/null +++ b/db/schemas/lib/triggers/drop/chimps_on_tikis.m4 @@ -0,0 +1,23 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Drop triggers for chimps_on_tikis table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS chimps_on_tikis_update_func() CASCADE; diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 9c7b467..610e354 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -258,6 +258,20 @@ sdb_generated_rst()dnl .. |CERTAINTIES.Description| replace:: :ref:`Description ` +.. |CHIMPS_ON_TIKIS| replace:: :ref:`CHIMPS_ON_TIKIS ` +.. |CHIMPS_ON_TIKIS.ID| replace:: + :ref:`ID ` +.. |CHIMPS_ON_TIKIS.AnimID| replace:: + :ref:`AnimID ` +.. |CHIMPS_ON_TIKIS.CommID| replace:: + :ref:`CommID ` +.. |CHIMPS_ON_TIKIS.TikiCreationDate| replace:: + :ref:`TikiCreationDate ` +.. |CHIMPS_ON_TIKIS.TikiDeploymentDate| replace:: + :ref:`TikiDeploymentDate ` +.. |CHIMPS_ON_TIKIS.Newcomer| replace:: + :ref:`Newcomer ` + .. |COLOBUS| replace:: :ref:`COLOBUS ` .. |COLOBUS.EID| replace:: :ref:`EID ` diff --git a/doc/src/housekeeping.m4 b/doc/src/housekeeping.m4 index f83bd29..d704b6a 100644 --- a/doc/src/housekeeping.m4 +++ b/doc/src/housekeeping.m4 @@ -34,3 +34,4 @@ The tables in this section are all in the ``housekeeping`` schema. :maxdepth: 3 housekeeping/aggression_event_log.rst + housekeeping/chimps_on_tikis.rst diff --git a/doc/src/housekeeping/chimps_on_tikis.m4 b/doc/src/housekeeping/chimps_on_tikis.m4 new file mode 100644 index 0000000..1fe1db3 --- /dev/null +++ b/doc/src/housekeeping/chimps_on_tikis.m4 @@ -0,0 +1,117 @@ +.. Copyright (C) 2026 The Meme Factory, Inc. www.karlpinc.com + + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as + published by the Free Software Foundation, either version 3 of the + License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see . + +.. M4 setup +include(constants.m4)dnl +include(macros.m4)dnl +sdb_rst_quotes(`on')dnl +sdb_generated_rst()dnl + +.. _CHIMPS_ON_TIKIS: + +CHIMPS_ON_TIKIS +--------------- + +.. |CHIMPS_ON_TIKIS_summary| replace:: + + Each row records when a chimpanzee identifier, a + |CHIMPS_ON_TIKIS.AnimID|, was on a community's T&G Tiki sheet. + There is, at most, one row per-community, per-Tiki creation date, + per individual. + +|CHIMPS_ON_TIKIS_summary| +The combination of |CHIMPS_ON_TIKIS.CommID|, +|CHIMPS_ON_TIKIS.TikiCreationDate|, and |CHIMPS_ON_TIKIS.AnimID| must +be unique. + + +.. contents:: + :depth: 2 + + +.. _CHIMPS_ON_TIKIS.ID: + +ID (IDentifier) +``````````````` + +.. |CHIMPS_ON_TIKIS.ID_summary| replace:: |idcol| + +|CHIMPS_ON_TIKIS.ID_summary| |notnull| + + +.. _CHIMPS_ON_TIKIS.AnimID: + +AnimID +`````` + +.. |CHIMPS_ON_TIKIS.AnimID_summary| replace:: + The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| that appeared on the + Tiki sheet. + +|CHIMPS_ON_TIKIS.AnimID_summary| +|notnull| + + +.. _CHIMPS_ON_TIKIS.CommID: + +CommID +`````` + +.. |CHIMPS_ON_TIKIS.CommID_summary| replace:: + The community the Tiki sheet was prepared for. + A |COMM_IDS|.\ |COMM_IDS.CommID| value. + +|CHIMPS_ON_TIKIS.CommID_summary| + +|notnull| + + +.. _CHIMPS_ON_TIKIS.TikiCreationDate: + +TikiCreationDate +```````````````` + +.. |CHIMPS_ON_TIKIS.TikiCreationDate_summary| replace:: + The date the version of the Tiki sheet was created. + +|CHIMPS_ON_TIKIS.TikiCreationDate_summary| +|notnull| + + +.. _CHIMPS_ON_TIKIS.TikiDeploymentDate: + +TikiDeploymentDate +`````````````````` + +.. |CHIMPS_ON_TIKIS.TikiDeploymentDate_summary| replace:: + The date the version of the Tiki sheet was first used. + +|CHIMPS_ON_TIKIS.TikiDeploymentDate_summary| +|notnull| + + +.. _CHIMPS_ON_TIKIS.Newcomer: + +Newcomer +```````` + +.. |CHIMPS_ON_TIKIS.Newcomer_summary| replace:: + A |boolean| value. + |true| when the |CHIMPS_ON_TIKIS.AnimID| had not previously + appeared on the community's (the community identified by the + |CHIMPS_ON_TIKIS.CommID|) Tiki sheet. + +|CHIMPS_ON_TIKIS.Newcomer_summary| +|notnull| -- 2.34.1